﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IPR_StudyMeetingMrgSys.DBUtility;//Please add references
namespace IPR_StudyMeetingMrgSys.DAL
{
	/// <summary>
	/// 数据访问类:UserInfo
	/// </summary>
	public partial class UserInfo
	{
		public UserInfo()
		{}
		#region  BasicMethod



		/// <summary>
		/// 增加一条数据
		/// </summary>
		public long Add(IPR_StudyMeetingMrgSys.Model.UserInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into UserInfo(");
			strSql.Append("UserID,UserPwd,UserSFZH,UserNo,UserSGZH,UserName,DeptID,Post,Sex,Nation,WorkBeforeEdu,CurrentEdu,Politic,Birthday,BeginWorkDate,AppointmentDate,InTime,EmployeeStatus,TechnicalGrade,TechnicalPost,IsGT,IsBZZ,PostStatus,isTYZYGZ,IsZYXCGZ,define1,define2,define3,define4,deefine5,UserID7,IsInputFinger,TeacherFinger1,TeacherFinger2,TeacherFinger3,DriverType,DriverLicense,RoleID,Groups,GroupsOrderNum)");
			strSql.Append(" values (");
			strSql.Append("@UserID,@UserPwd,@UserSFZH,@UserNo,@UserSGZH,@UserName,@DeptID,@Post,@Sex,@Nation,@WorkBeforeEdu,@CurrentEdu,@Politic,@Birthday,@BeginWorkDate,@AppointmentDate,@InTime,@EmployeeStatus,@TechnicalGrade,@TechnicalPost,@IsGT,@IsBZZ,@PostStatus,@isTYZYGZ,@IsZYXCGZ,@define1,@define2,@define3,@define4,@deefine5,@UserID7,@IsInputFinger,@TeacherFinger1,@TeacherFinger2,@TeacherFinger3,@DriverType,@DriverLicense,@RoleID,@Groups,@GroupsOrderNum)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.NVarChar,20),
					new SqlParameter("@UserPwd", SqlDbType.NVarChar,15),
					new SqlParameter("@UserSFZH", SqlDbType.NVarChar,20),
					new SqlParameter("@UserNo", SqlDbType.NVarChar,15),
					new SqlParameter("@UserSGZH", SqlDbType.NVarChar,20),
					new SqlParameter("@UserName", SqlDbType.NVarChar,10),
					new SqlParameter("@DeptID", SqlDbType.Int,4),
					new SqlParameter("@Post", SqlDbType.NVarChar,20),
					new SqlParameter("@Sex", SqlDbType.NVarChar,2),
					new SqlParameter("@Nation", SqlDbType.NVarChar,10),
					new SqlParameter("@WorkBeforeEdu", SqlDbType.NVarChar,20),
					new SqlParameter("@CurrentEdu", SqlDbType.NVarChar,20),
					new SqlParameter("@Politic", SqlDbType.NVarChar,10),
					new SqlParameter("@Birthday", SqlDbType.NVarChar,20),
					new SqlParameter("@BeginWorkDate", SqlDbType.NVarChar,20),
					new SqlParameter("@AppointmentDate", SqlDbType.NVarChar,20),
					new SqlParameter("@InTime", SqlDbType.NVarChar,20),
					new SqlParameter("@EmployeeStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@TechnicalGrade", SqlDbType.NVarChar,20),
					new SqlParameter("@TechnicalPost", SqlDbType.NVarChar,20),
					new SqlParameter("@IsGT", SqlDbType.Int,4),
					new SqlParameter("@IsBZZ", SqlDbType.Int,4),
					new SqlParameter("@PostStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@isTYZYGZ", SqlDbType.Int,4),
					new SqlParameter("@IsZYXCGZ", SqlDbType.Int,4),
					new SqlParameter("@define1", SqlDbType.NVarChar,50),
					new SqlParameter("@define2", SqlDbType.NVarChar,50),
					new SqlParameter("@define3", SqlDbType.NVarChar,500),
					new SqlParameter("@define4", SqlDbType.NVarChar,500),
					new SqlParameter("@deefine5", SqlDbType.NVarChar,500),
					new SqlParameter("@UserID7", SqlDbType.NVarChar,7),
					new SqlParameter("@IsInputFinger", SqlDbType.Int,4),
					new SqlParameter("@TeacherFinger1", SqlDbType.Image),
					new SqlParameter("@TeacherFinger2", SqlDbType.Image),
					new SqlParameter("@TeacherFinger3", SqlDbType.Image),
					new SqlParameter("@DriverType", SqlDbType.NVarChar,20),
					new SqlParameter("@DriverLicense", SqlDbType.NVarChar,20),
                    new SqlParameter("@RoleID", SqlDbType.Int,4),
                    new SqlParameter("@Groups", SqlDbType.NVarChar,20),
                    new SqlParameter("@GroupsOrderNum", SqlDbType.Int,4)};
			parameters[0].Value = model.UserID;
			parameters[1].Value = model.UserPwd;
			parameters[2].Value = model.UserSFZH;
			parameters[3].Value = model.UserNo;
			parameters[4].Value = model.UserSGZH;
			parameters[5].Value = model.UserName;
			parameters[6].Value = model.DeptID;
			parameters[7].Value = model.Post;
			parameters[8].Value = model.Sex;
			parameters[9].Value = model.Nation;
			parameters[10].Value = model.WorkBeforeEdu;
			parameters[11].Value = model.CurrentEdu;
			parameters[12].Value = model.Politic;
			parameters[13].Value = model.Birthday;
			parameters[14].Value = model.BeginWorkDate;
			parameters[15].Value = model.AppointmentDate;
			parameters[16].Value = model.InTime;
			parameters[17].Value = model.EmployeeStatus;
			parameters[18].Value = model.TechnicalGrade;
			parameters[19].Value = model.TechnicalPost;
			parameters[20].Value = model.IsGT;
			parameters[21].Value = model.IsBZZ;
			parameters[22].Value = model.PostStatus;
			parameters[23].Value = model.isTYZYGZ;
			parameters[24].Value = model.IsZYXCGZ;
			parameters[25].Value = model.define1;
			parameters[26].Value = model.define2;
			parameters[27].Value = model.define3;
			parameters[28].Value = model.define4;
			parameters[29].Value = model.deefine5;
			parameters[30].Value = model.UserID7;
			parameters[31].Value = model.IsInputFinger;
			parameters[32].Value = model.TeacherFinger1;
			parameters[33].Value = model.TeacherFinger2;
			parameters[34].Value = model.TeacherFinger3;
			parameters[35].Value = model.DriverType;
            parameters[36].Value = model.DriverLicense;
            parameters[37].Value = model.RoleID;
            parameters[38].Value = model.Groups;
            parameters[39].Value = model.GroupsOrderNum;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt64(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(IPR_StudyMeetingMrgSys.Model.UserInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update UserInfo set ");
			strSql.Append("UserID=@UserID,");
			strSql.Append("UserPwd=@UserPwd,");
			strSql.Append("UserSFZH=@UserSFZH,");
			strSql.Append("UserNo=@UserNo,");
			strSql.Append("UserSGZH=@UserSGZH,");
			strSql.Append("UserName=@UserName,");
			strSql.Append("DeptID=@DeptID,");
			strSql.Append("Post=@Post,");
			strSql.Append("Sex=@Sex,");
			strSql.Append("Nation=@Nation,");
			strSql.Append("WorkBeforeEdu=@WorkBeforeEdu,");
			strSql.Append("CurrentEdu=@CurrentEdu,");
			strSql.Append("Politic=@Politic,");
			strSql.Append("Birthday=@Birthday,");
			strSql.Append("BeginWorkDate=@BeginWorkDate,");
			strSql.Append("AppointmentDate=@AppointmentDate,");
			strSql.Append("InTime=@InTime,");
			strSql.Append("EmployeeStatus=@EmployeeStatus,");
			strSql.Append("TechnicalGrade=@TechnicalGrade,");
			strSql.Append("TechnicalPost=@TechnicalPost,");
			strSql.Append("IsGT=@IsGT,");
			strSql.Append("IsBZZ=@IsBZZ,");
			strSql.Append("PostStatus=@PostStatus,");
			strSql.Append("isTYZYGZ=@isTYZYGZ,");
			strSql.Append("IsZYXCGZ=@IsZYXCGZ,");
			strSql.Append("define1=@define1,");
			strSql.Append("define2=@define2,");
			strSql.Append("define3=@define3,");
			strSql.Append("define4=@define4,");
			strSql.Append("deefine5=@deefine5,");
			strSql.Append("UserID7=@UserID7,");
			strSql.Append("IsInputFinger=@IsInputFinger,");
			strSql.Append("TeacherFinger1=@TeacherFinger1,");
			strSql.Append("TeacherFinger2=@TeacherFinger2,");
			strSql.Append("TeacherFinger3=@TeacherFinger3,");
			strSql.Append("DriverType=@DriverType,");
            strSql.Append("DriverLicense=@DriverLicense,");
            strSql.Append("Groups=@Groups,");
            strSql.Append("RoleID=@RoleID");
            strSql.Append(" where FID=@FID");
			SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.NVarChar,20),
					new SqlParameter("@UserPwd", SqlDbType.NVarChar,15),
					new SqlParameter("@UserSFZH", SqlDbType.NVarChar,20),
					new SqlParameter("@UserNo", SqlDbType.NVarChar,15),
					new SqlParameter("@UserSGZH", SqlDbType.NVarChar,20),
					new SqlParameter("@UserName", SqlDbType.NVarChar,10),
					new SqlParameter("@DeptID", SqlDbType.Int,4),
					new SqlParameter("@Post", SqlDbType.NVarChar,20),
					new SqlParameter("@Sex", SqlDbType.NVarChar,2),
					new SqlParameter("@Nation", SqlDbType.NVarChar,10),
					new SqlParameter("@WorkBeforeEdu", SqlDbType.NVarChar,20),
					new SqlParameter("@CurrentEdu", SqlDbType.NVarChar,20),
					new SqlParameter("@Politic", SqlDbType.NVarChar,10),
					new SqlParameter("@Birthday", SqlDbType.NVarChar,20),
					new SqlParameter("@BeginWorkDate", SqlDbType.NVarChar,20),
					new SqlParameter("@AppointmentDate", SqlDbType.NVarChar,20),
					new SqlParameter("@InTime", SqlDbType.NVarChar,20),
					new SqlParameter("@EmployeeStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@TechnicalGrade", SqlDbType.NVarChar,20),
					new SqlParameter("@TechnicalPost", SqlDbType.NVarChar,20),
					new SqlParameter("@IsGT", SqlDbType.Int,4),
					new SqlParameter("@IsBZZ", SqlDbType.Int,4),
					new SqlParameter("@PostStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@isTYZYGZ", SqlDbType.Int,4),
					new SqlParameter("@define1", SqlDbType.NVarChar,50),
					new SqlParameter("@define2", SqlDbType.NVarChar,50),
					new SqlParameter("@define3", SqlDbType.NVarChar,500),
					new SqlParameter("@define4", SqlDbType.NVarChar,500),
					new SqlParameter("@deefine5", SqlDbType.NVarChar,500),
					new SqlParameter("@UserID7", SqlDbType.NVarChar,7),
                    new SqlParameter("@IsInputFinger", SqlDbType.Int,4),
                    new SqlParameter("@IsInputFinger", SqlDbType.Int,4),
                    new SqlParameter("@TeacherFinger1", SqlDbType.Image),
					new SqlParameter("@TeacherFinger2", SqlDbType.Image),
					new SqlParameter("@TeacherFinger3", SqlDbType.Image),
					new SqlParameter("@DriverType", SqlDbType.NVarChar,20),
					new SqlParameter("@DriverLicense", SqlDbType.NVarChar,20),
					new SqlParameter("@FID", SqlDbType.BigInt,8),
					new SqlParameter("@RoleID", SqlDbType.Int,4),

                    new SqlParameter("@Groups", SqlDbType.NVarChar,20)};
			parameters[0].Value = model.UserID;
			parameters[1].Value = model.UserPwd;
			parameters[2].Value = model.UserSFZH;
			parameters[3].Value = model.UserNo;
			parameters[4].Value = model.UserSGZH;
			parameters[5].Value = model.UserName;
			parameters[6].Value = model.DeptID;
			parameters[7].Value = model.Post;
			parameters[8].Value = model.Sex;
			parameters[9].Value = model.Nation;
			parameters[10].Value = model.WorkBeforeEdu;
			parameters[11].Value = model.CurrentEdu;
			parameters[12].Value = model.Politic;
			parameters[13].Value = model.Birthday;
			parameters[14].Value = model.BeginWorkDate;
			parameters[15].Value = model.AppointmentDate;
			parameters[16].Value = model.InTime;
			parameters[17].Value = model.EmployeeStatus;
			parameters[18].Value = model.TechnicalGrade;
			parameters[19].Value = model.TechnicalPost;
			parameters[20].Value = model.IsGT;
			parameters[21].Value = model.IsBZZ;
			parameters[22].Value = model.PostStatus;
			parameters[23].Value = model.isTYZYGZ;
			parameters[24].Value = model.IsZYXCGZ;
			parameters[25].Value = model.define1;
			parameters[26].Value = model.define2;
			parameters[27].Value = model.define3;
			parameters[28].Value = model.define4;
			parameters[29].Value = model.deefine5;
			parameters[30].Value = model.UserID7;
			parameters[31].Value = model.IsInputFinger;
			parameters[32].Value = model.TeacherFinger1;
			parameters[33].Value = model.TeacherFinger2;
			parameters[34].Value = model.TeacherFinger3;
			parameters[35].Value = model.DriverType;
			parameters[36].Value = model.DriverLicense;
            parameters[37].Value = model.FID;
            parameters[38].Value = model.RoleID;
            parameters[39].Value = model.Groups;

            int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(long FID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from UserInfo ");
			strSql.Append(" where FID=@FID");
			SqlParameter[] parameters = {
					new SqlParameter("@FID", SqlDbType.BigInt)
			};
			parameters[0].Value = FID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string FIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from UserInfo ");
			strSql.Append(" where FID in ("+FIDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.UserInfo GetModel(long FID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 FID,UserID,UserPwd,UserSFZH,UserNo,UserSGZH,UserName,DeptID,Post,Sex,Nation,WorkBeforeEdu,CurrentEdu,Politic,Birthday,BeginWorkDate,AppointmentDate,InTime,EmployeeStatus,TechnicalGrade,TechnicalPost,IsGT,IsBZZ,PostStatus,isTYZYGZ,IsZYXCGZ,define1,define2,define3,define4,deefine5,UserID7,IsInputFinger,TeacherFinger1,TeacherFinger2,TeacherFinger3,DriverType,DriverLicense,RoleID,Groups from UserInfo ");
			strSql.Append(" where FID=@FID");
			SqlParameter[] parameters = {
					new SqlParameter("@FID", SqlDbType.BigInt)
			};
			parameters[0].Value = FID;

			IPR_StudyMeetingMrgSys.Model.UserInfo model=new IPR_StudyMeetingMrgSys.Model.UserInfo();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.UserInfo DataRowToModel(DataRow row)
		{
			IPR_StudyMeetingMrgSys.Model.UserInfo model=new IPR_StudyMeetingMrgSys.Model.UserInfo();
			if (row != null)
            {
                if (row["RoleID"] != null && row["RoleID"].ToString() != "")
                {
                    model.RoleID = int.Parse(row["RoleID"].ToString());
                }
                if (row["FID"] != null && row["FID"].ToString() != "")
                {
                    model.FID = long.Parse(row["FID"].ToString());
                }
                if (row["UserID"]!=null)
				{
					model.UserID=row["UserID"].ToString();
				}
				if(row["UserPwd"]!=null)
				{
					model.UserPwd=row["UserPwd"].ToString();
				}
				if(row["UserSFZH"]!=null)
				{
					model.UserSFZH=row["UserSFZH"].ToString();
				}
				if(row["UserNo"]!=null)
				{
					model.UserNo=row["UserNo"].ToString();
				}
				if(row["UserSGZH"]!=null)
				{
					model.UserSGZH=row["UserSGZH"].ToString();
				}
				if(row["UserName"]!=null)
				{
					model.UserName=row["UserName"].ToString();
				}
				if(row["DeptID"]!=null && row["DeptID"].ToString()!="")
				{
					model.DeptID=int.Parse(row["DeptID"].ToString());
				}
				if(row["Post"]!=null)
				{
					model.Post=row["Post"].ToString();
				}
				if(row["Sex"]!=null)
				{
					model.Sex=row["Sex"].ToString();
				}
				if(row["Nation"]!=null)
				{
					model.Nation=row["Nation"].ToString();
				}
				if(row["WorkBeforeEdu"]!=null)
				{
					model.WorkBeforeEdu=row["WorkBeforeEdu"].ToString();
				}
				if(row["CurrentEdu"]!=null)
				{
					model.CurrentEdu=row["CurrentEdu"].ToString();
				}
				if(row["Politic"]!=null)
				{
					model.Politic=row["Politic"].ToString();
				}
				if(row["Birthday"]!=null)
				{
					model.Birthday=row["Birthday"].ToString();
				}
				if(row["BeginWorkDate"]!=null)
				{
					model.BeginWorkDate=row["BeginWorkDate"].ToString();
				}
				if(row["AppointmentDate"]!=null)
				{
					model.AppointmentDate=row["AppointmentDate"].ToString();
				}
				if(row["InTime"]!=null)
				{
					model.InTime=row["InTime"].ToString();
				}
				if(row["EmployeeStatus"]!=null)
				{
					model.EmployeeStatus=row["EmployeeStatus"].ToString();
				}
				if(row["TechnicalGrade"]!=null)
				{
					model.TechnicalGrade=row["TechnicalGrade"].ToString();
				}
				if(row["TechnicalPost"]!=null)
				{
					model.TechnicalPost=row["TechnicalPost"].ToString();
				}
				if(row["IsGT"]!=null && row["IsGT"].ToString()!="")
				{
					model.IsGT=int.Parse(row["IsGT"].ToString());
				}
				if(row["IsBZZ"]!=null && row["IsBZZ"].ToString()!="")
				{
					model.IsBZZ=int.Parse(row["IsBZZ"].ToString());
				}
				if(row["PostStatus"]!=null)
				{
					model.PostStatus=row["PostStatus"].ToString();
				}
				if(row["isTYZYGZ"]!=null && row["isTYZYGZ"].ToString()!="")
				{
					model.isTYZYGZ=int.Parse(row["isTYZYGZ"].ToString());
				}
				if(row["IsZYXCGZ"]!=null && row["IsZYXCGZ"].ToString()!="")
				{
					model.IsZYXCGZ=int.Parse(row["IsZYXCGZ"].ToString());
				}
				if(row["define1"]!=null)
				{
					model.define1=row["define1"].ToString();
				}
				if(row["define2"]!=null)
				{
					model.define2=row["define2"].ToString();
				}
				if(row["define3"]!=null)
				{
					model.define3=row["define3"].ToString();
				}
				if(row["define4"]!=null)
				{
					model.define4=row["define4"].ToString();
				}
				if(row["deefine5"]!=null)
				{
					model.deefine5=row["deefine5"].ToString();
				}
				if(row["UserID7"]!=null)
				{
					model.UserID7=row["UserID7"].ToString();
				}
				if(row["IsInputFinger"]!=null && row["IsInputFinger"].ToString()!="")
				{
					model.IsInputFinger=int.Parse(row["IsInputFinger"].ToString());
				}
				if(row["TeacherFinger1"]!=null && row["TeacherFinger1"].ToString()!="")
				{
					model.TeacherFinger1=(byte[])row["TeacherFinger1"];
				}
				if(row["TeacherFinger2"]!=null && row["TeacherFinger2"].ToString()!="")
				{
					model.TeacherFinger2=(byte[])row["TeacherFinger2"];
				}
				if(row["TeacherFinger3"]!=null && row["TeacherFinger3"].ToString()!="")
				{
					model.TeacherFinger3=(byte[])row["TeacherFinger3"];
				}
				if(row["DriverType"]!=null)
				{
					model.DriverType=row["DriverType"].ToString();
                }
                if (row["DriverLicense"] != null)
                {
                    model.DriverLicense = row["DriverLicense"].ToString();
                }
                if (row["Groups"] != null)
                {
                    model.Groups = row["Groups"].ToString();
                }
            }
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select FID,UserID,UserPwd,UserSFZH,UserNo,UserSGZH,UserName,DeptID,Post,Sex,Nation,WorkBeforeEdu,CurrentEdu,Politic,Birthday,BeginWorkDate,AppointmentDate,InTime,EmployeeStatus,TechnicalGrade,TechnicalPost,IsGT,IsBZZ,PostStatus,isTYZYGZ,IsZYXCGZ,define1,define2,define3,define4,deefine5,UserID7,IsInputFinger,TeacherFinger1,TeacherFinger2,TeacherFinger3,DriverType,DriverLicense,RoleID,Groups ");
			strSql.Append(" FROM UserInfo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" FID,UserID,UserPwd,UserSFZH,UserNo,UserSGZH,UserName,DeptID,Post,Sex,Nation,WorkBeforeEdu,CurrentEdu,Politic,Birthday,BeginWorkDate,AppointmentDate,InTime,EmployeeStatus,TechnicalGrade,TechnicalPost,IsGT,IsBZZ,PostStatus,isTYZYGZ,IsZYXCGZ,define1,define2,define3,define4,deefine5,UserID7,IsInputFinger,TeacherFinger1,TeacherFinger2,TeacherFinger3,DriverType,DriverLicense,RoleID,Groups ");
			strSql.Append(" FROM UserInfo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM UserInfo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.FID desc");
			}
			strSql.Append(")AS Row, T.*  from UserInfo T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

        /*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "UserInfo";
			parameters[1].Value = "FID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where UserID=@UserID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@UserID", SqlDbType.NVarChar,15)  };
            parameters[0].Value = UserID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string UserID, string UserSFZH, string UserNo, long FID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where UserID=@UserID and UserSFZH=@UserSFZH and UserNo=@UserNo and FID=@FID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@UserID", SqlDbType.NVarChar,15),
                    new SqlParameter("@UserSFZH", SqlDbType.NVarChar,20),
                    new SqlParameter("@UserNo", SqlDbType.NVarChar,15),
                    new SqlParameter("@FID", SqlDbType.BigInt,8)            };
            parameters[0].Value = UserID;
            parameters[1].Value = UserSFZH;
            parameters[2].Value = UserNo;
            parameters[3].Value = FID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string UserID, string UserSFZH, string UserNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where UserID=@UserID or UserSFZH=@UserSFZH or UserNo=@UserNo ");
            SqlParameter[] parameters = {
                    new SqlParameter("@UserID", SqlDbType.NVarChar,15),
                    new SqlParameter("@UserSFZH", SqlDbType.NVarChar,20),
                    new SqlParameter("@UserNo", SqlDbType.NVarChar,15)
            };
            parameters[0].Value = UserID;
            parameters[1].Value = UserSFZH;
            parameters[2].Value = UserNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 得到一个对象实体(根据EmpID)
        /// </summary>
        public DataTable GetEmployeeDetail(int EID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT t.FID,UserID,UserPwd,UserSFZH,UserNo,UserSGZH,UserName,DeptID,Post,Sex,Nation,WorkBeforeEdu,CurrentEdu,Politic,Birthday,BeginWorkDate,AppointmentDate,InTime,EmployeeStatus, TechnicalGrade, TechnicalPost, IsGT, IsBZZ, PostStatus, isTYZYGZ, IsZYXCGZ, define1, define2, define3, define4, deefine5, UserID7, IsInputFinger, TeacherFinger1, TeacherFinger2, TeacherFinger3, DriverType, DriverLicense, Groups,t2.DepName,t.RoleID,T3.RoleName  FROM dbo.UserInfo t   left join Dep t2 on t.DeptID = t2.DepID");
            //strSql.Append(" left join Dep T1 on t.DepID=T1.DepID ");
            strSql.Append(" left join Role T3 on t.RoleID = T3.RoleID ");
            //strSql.Append(" left join JWDInfo T3 on t.EmpJWDID = T3.JWDID ");
            strSql.Append(" where t.FID=@EID");
            SqlParameter[] parameters = {
                    new SqlParameter("@EID", SqlDbType.Int)
            };
            parameters[0].Value = EID;

            IPR_StudyMeetingMrgSys.Model.UserInfo model = new IPR_StudyMeetingMrgSys.Model.UserInfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0];
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string UserID, string UserSFZH, string UserNo, long FID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from UserInfo ");
            strSql.Append(" where UserID=@UserID and UserSFZH=@UserSFZH and UserNo=@UserNo and FID=@FID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@UserID", SqlDbType.NVarChar,15),
                    new SqlParameter("@UserSFZH", SqlDbType.NVarChar,20),
                    new SqlParameter("@UserNo", SqlDbType.NVarChar,15),
                    new SqlParameter("@FID", SqlDbType.BigInt,8)            };
            parameters[0].Value = UserID;
            parameters[1].Value = UserSFZH;
            parameters[2].Value = UserNo;
            parameters[3].Value = FID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public IPR_StudyMeetingMrgSys.Model.UserInfo GetModel(string where)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FID,UserID,UserPwd,UserSFZH,UserNo,UserSGZH,UserName,DeptID,Post,Sex,Nation,WorkBeforeEdu,CurrentEdu,Politic,Birthday,BeginWorkDate,AppointmentDate,InTime,EmployeeStatus,TechnicalGrade,TechnicalPost,IsGT,IsBZZ,PostStatus,isTYZYGZ,IsZYXCGZ,define1,define2,define3,define4,deefine5,UserID7,IsInputFinger,TeacherFinger1,TeacherFinger2,TeacherFinger3,DriverType,DriverLicense,RoleID,Groups from UserInfo ");
            if (where != "")
                strSql.Append(" where  " + where);

            IPR_StudyMeetingMrgSys.Model.UserInfo model = new IPR_StudyMeetingMrgSys.Model.UserInfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int rowCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.FID desc");
            }
            strSql.Append(")AS Row, T.*,t1.DepName,t2.RoleName,case when (Finger is NULL AND Finger1 IS NULL) then '未录' else '已录' end as 'IsHaveFinger' from UserInfo T ");
            strSql.Append(" left join Dep t1 on t.DeptID=t1.DepID ");
            strSql.Append(" left join Role t2 on T.RoleID=t2.RoleID ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");

            rowCount = 0;
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
                if (ds.Tables.Count > 0)
                    if (ds.Tables[0].Rows.Count > 0)
                        rowCount = ds.Tables[0].Rows.Count;


            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);


            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="OriPwd">原密码</param>
        /// <param name="NewPwd">新密码</param>
        /// <param name="errMsg">错误信息</param>
        /// <returns></returns>
        public bool UpdatePwd(string OriPwd,string NewPwd,string SureUserPwd, string UserId,out string errMsg)
        {
            bool isContinue = true; errMsg =string.Empty;
            #region 检测原密码输入是否正确
            if (OriPwd==NewPwd& isContinue)
            {
                isContinue = false; errMsg = "新密码不能与原密码相同!";
            }
            if (SureUserPwd!= NewPwd& isContinue)
            {
                isContinue = false; errMsg = "两次输入密码不相同!";
            }
            if(isContinue)
            {
                IPR_StudyMeetingMrgSys.Model.TrainPlan obj = new IPR_StudyMeetingMrgSys.Model.TrainPlan();
                StringBuilder strSql_select = new StringBuilder();
                strSql_select.Append("select  top 1 UserPwd from UserInfo");
                strSql_select.Append(" where UserID=@UserID");
                SqlParameter[] parameters_select = {
                    new SqlParameter("@UserID", SqlDbType.NVarChar)
                };
                parameters_select[0].Value = UserId;
                DataSet ds = DbHelperSQL.Query(strSql_select.ToString(), parameters_select);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    var item = ds.Tables[0].Rows[0];
                    if (item["UserPwd"] != null && item["UserPwd"].ToString() != "")
                    {
                        string pwd = item["UserPwd"].ToString();
                        //比较密码  是否相同
                        if (OriPwd != pwd)
                        {
                            isContinue = false; errMsg = "请正确输入原密码!";
                        }
                    }
                }
            }
            #endregion
            if (isContinue)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update UserInfo set ");
                strSql.Append("UserPwd=@UserPwd");
                strSql.Append(" where UserID=@UserID");
                SqlParameter[] parameters = {
                    new SqlParameter("@UserID", SqlDbType.NVarChar,20),
                    new SqlParameter("@UserPwd", SqlDbType.NVarChar,15)
                  };
                parameters[0].Value = UserId;
                parameters[1].Value = NewPwd;
                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            }
            return isContinue;
        }
         
        /// <summary>
        /// 根据部门ID，获取部门所有的人员信息
        /// </summary>
        /// <param name="DeptIDS"></param>
        /// <returns></returns>
        public DataTable GetDeptUserForDeptID(string DeptIDS)
        {
            bool blExist = false;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from UserInfo where DeptID in ("+DeptIDS+")"); 
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
                if (ds.Tables.Count > 0)
                    if (ds.Tables[0].Rows.Count > 0)
                        blExist = true;
            if (blExist)
                return ds.Tables[0];
            else return null;
        }

        public bool UpdateUserFinger(string UserID, string Finger,string PhotoString, string sFingerIsUpdateOrAdd)
        {
            StringBuilder strSql = new StringBuilder();
            //if (sFingerIsUpdateOrAdd == "U")
            //{
                strSql.Append("update UserInfo set ");
                strSql.Append("Finger=@Finger,");
                strSql.Append("Photo=@Photo");
                strSql.Append(" where UserID=@UserID");
            //}
            //else if(sFingerIsUpdateOrAdd=="A")
            //{
            //    strSql.Append("update UserInfo set ");
            //    strSql.Append("Finger1=@Finger,");
            //    strSql.Append("Photo=@Photo");
            //    strSql.Append(" where UserID=@UserID");
            //}
            SqlParameter[] parameters = {
                new SqlParameter("@UserID", SqlDbType.NVarChar,20),
                new SqlParameter("@Finger", SqlDbType.NVarChar,1024),
                new SqlParameter("@Photo", SqlDbType.NVarChar,1024)
                };
            parameters[0].Value = UserID;
            parameters[1].Value = Finger;
            parameters[2].Value = PhotoString;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion  ExtensionMethod
    }
}

